package sql;

import java.io.*;
import java.sql.*;
import java.util.Calendar;
import java.util.Vector;

import model.AppointmentDataModel;
import model.CategoryDataModel;

/**
 * 07-01-2010
 * 
 * This class contains the SQL queries and is responsible for the connection with the database.
 * 
 * @author Maarten Ezendam & Timo Janssen & Stijn Kliemesch
 */
public class SqlConnector {

	private Connection conn;
	private PreparedStatement prep;
	private ResultSet rs;
	private String databaseName;

	/**
	 * Default Constructor.
	 */
	public SqlConnector() {	
		this("data.db");
	}

	/**
	 * Database connectie.
	 * 
	 * @param aDatabaseName
	 */
	public SqlConnector( String aDatabaseName ) {	
		databaseName = aDatabaseName;
		createDatabase();
	}

	/**
	 * Function to create a database.
	 */
	public void createDatabase() {
		if(!(new File(databaseName)).exists()) {
			createTable();
		}
	}

	/**
	 * Function to create the table Appointment and Category.
	 */
	public void createTable() {
		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:" + databaseName);
			prep = conn.prepareStatement("drop table if exists APPOINTMENT;");
			prep.executeUpdate();
			prep = conn.prepareStatement("drop table if exists CATEGORY;");
			prep.executeUpdate();
			prep = conn.prepareStatement("create table APPOINTMENT (Id INTEGER PRIMARY KEY, Title TEXT NOT NULL, "
					+ "Start TEXT NOT NULL, End TEXT NOT NULL, Location TEXT, Contactperson TEXT, Description TEXT," 
					+ "Category_id INTEGER);");
			prep.executeUpdate();
			prep = conn.prepareStatement("create table CATEGORY( Id INTEGER PRIMARY KEY, Title TEXT NOT NULL);");
			prep.executeUpdate();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	// All appointment functions

	/**
	 * Function to insert data in to table APPOINTMENT.
	 * 
	 * @param appointmentDataModel
	 */
	public void addAppointment(AppointmentDataModel appointmentDataModel) {
		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:"+ databaseName);
			PreparedStatement prep = conn.prepareStatement(
					"insert into APPOINTMENT (Title, Start, End, Location, Contactperson, Category_id, Description) values "
					+ "(?,?,?,?,?,?,?);");

			int categoryid = 0;

			if(appointmentDataModel.getCategory() != null) {
				categoryid = appointmentDataModel.getCategory().getId();
			}
			
			
			prep.setString(1, appointmentDataModel.getTitle());
			prep.setString(2, util.CalendarConvert.convertCalendarToDateString(appointmentDataModel.getStartDate()));
			prep.setString(3, util.CalendarConvert.convertCalendarToDateString(appointmentDataModel.getEndDate()));
			prep.setString(4, appointmentDataModel.getLocation());
			prep.setString(5, appointmentDataModel.getContact());
			prep.setInt(6, categoryid);
			prep.setString(7, appointmentDataModel.getDescription());

			prep.addBatch();

			conn.setAutoCommit(false);
			prep.executeBatch();
			conn.setAutoCommit(true);
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Function to select appointmentData from database.
	 * 
	 * @param searchId Contains the name of the table for the select query
	 * @return AppointmentDataModel
	 */
	public model.AppointmentDataModel selectAppointment(int searchId) {
		AppointmentDataModel wrapper = null;
		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:"+ databaseName);
			prep = conn.prepareStatement("select * from APPOINTMENT where id = ?;");
			prep.setInt(1, searchId);
			rs = prep.executeQuery();
			rs.next();

			Calendar startDate = util.CalendarConvert.convertDateStringToCalendar(rs.getString("start"));
			Calendar endDate = util.CalendarConvert.convertDateStringToCalendar(rs.getString("end"));

			int id = rs.getInt("id");
			String title = rs.getString("title");
			String location = rs.getString("location");
			String contactPerson = rs.getString("contactperson");
			String description = rs.getString("description");
			int catId = Integer.valueOf(rs.getString("Category_id"));

			conn.close();

			CategoryDataModel category = selectCategory(catId);

			wrapper = new model.AppointmentDataModel(id, title, startDate, endDate, location, contactPerson, category, description);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch(IllegalArgumentException e) {
			e.printStackTrace();
		}

		return wrapper;
	}

	/**
	 * Function for selecting all appointments from the database. An appointment is put in a wrapper and the wrapper
	 * is added to an Vector, which is returned at the end of the function.
	 * 
	 * @return Vector<AppointmentDataModel>
	 */
	public Vector<AppointmentDataModel> selectAllAppointments() {
		Vector<AppointmentDataModel> list = new Vector<AppointmentDataModel>();

		try {
			Vector<CategoryDataModel> categoryList = selectAllCategories();
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:"+ databaseName);
			prep = conn.prepareStatement("select * from APPOINTMENT;");
			rs = prep.executeQuery();
			while (rs.next()) {
				Calendar startDate = util.CalendarConvert.convertDateStringToCalendar(rs.getString("start"));
				Calendar endDate = util.CalendarConvert.convertDateStringToCalendar(rs.getString("end"));
				CategoryDataModel category = null;
				for(CategoryDataModel c : categoryList) {
					if(c.getId() == rs.getInt("Category_id")) {
						category = c;
					} else {
						c = null;
					}
				}
				list.add(new AppointmentDataModel(rs.getInt("id"),rs.getString("title"), startDate, endDate, rs.getString("location"), rs.getString("contactperson"), category,  rs.getString("description")));
			}
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch(IllegalArgumentException e) {
			e.printStackTrace();
		}

		return list;
	}

	/**
	 * Select all appointments that are between start and end date.
	 * 
	 * @param startDateRange
	 * @param endDateRange
	 * @return Vector<AppointmentDataModel>
	 */
	public Vector<AppointmentDataModel> selectAllAppointmentsInRange(Calendar startDateRange, Calendar endDateRange) {
		Vector<AppointmentDataModel> list = new Vector<AppointmentDataModel>();
		try {
			Vector<CategoryDataModel> categoryList = selectAllCategories();
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:"+ databaseName);

			prep = conn.prepareStatement("select * from APPOINTMENT");
			rs = prep.executeQuery();

			
			while (rs.next()) {
				Calendar startDate = util.CalendarConvert.convertDateStringToCalendar(rs.getString("start"));
				Calendar endDate = util.CalendarConvert.convertDateStringToCalendar(rs.getString("end"));
				
				if(startDateRange.compareTo(startDate) <= 0 &&  endDateRange.compareTo(endDate) >= 0) {
					CategoryDataModel category = null;
					for(CategoryDataModel c : categoryList) {
						if(c.getId() == rs.getInt("Category_id")) {
							category = c;
						} else {
							c = null;
						}
					}
					list.add(new AppointmentDataModel(rs.getInt("id"),rs.getString("title"), startDate, endDate, rs.getString("location"), rs.getString("contactperson"), category, rs.getString("description")));
				}
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch(Exception e) {
			e.printStackTrace();
		}

		return list;
	}

	/**
	 * Function to upload changes in an Appointment to the database.
	 * 
	 * @param appointmentDataModel
	 */
	public void updateAppointment(AppointmentDataModel appointmentDataModel) {
		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:"+ databaseName);
			prep = conn.prepareStatement(
					"update APPOINTMENT set title = ?, start = ?, end = ?, location = ?, contactperson = ?,"
					+ "Category_id = ?, description = ? where id = ?;");

			int categoryid = 0;

			if(appointmentDataModel.getCategory() != null) {
				categoryid = appointmentDataModel.getCategory().getId();
			}

			prep.setString(1, appointmentDataModel.getTitle());
			prep.setString(2, util.CalendarConvert.convertCalendarToDateString(appointmentDataModel.getStartDate()));
			prep.setString(3, util.CalendarConvert.convertCalendarToDateString(appointmentDataModel.getEndDate()));
			prep.setString(4, appointmentDataModel.getLocation());
			prep.setString(5, appointmentDataModel.getContact());
			prep.setInt(6, categoryid);
			prep.setString(7, appointmentDataModel.getDescription());
			prep.setInt(8, appointmentDataModel.getId());
			prep.executeUpdate();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Function to remove an appointment from the database.
	 * 
	 * @param id
	 */
	public void deleteAppointment(int id) {
		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:"+ databaseName);
			prep = conn.prepareStatement(
			"delete from APPOINTMENT where id = ?;");
			prep.setInt(1, id);
			prep.executeUpdate();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Function to check if an appointment exists in the database.
	 * 
	 * @param id
	 * @return boolean
	 */
	public boolean appointmentExists(int id) {
		boolean aExists = false;
		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:"+ databaseName);
			prep = conn.prepareStatement("select * from APPOINTMENT where id = ?;");
			prep.setInt(1, id);
			rs = prep.executeQuery();
			aExists = rs.next();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return aExists;
	}

	/**
	 * Function to search for appointments.
	 * 
	 * @param keyword
	 * @return Vector<AppointmentDataModel>
	 */
	public Vector<AppointmentDataModel> search(String keyword) {

		Vector<AppointmentDataModel> result = new Vector<AppointmentDataModel>();

		try {
			Vector<CategoryDataModel> categoryList = selectAllCategories();
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:" + databaseName);
			prep = conn.prepareStatement("select a.id as a_id, "
					+ "a.title as a_title, "
					+ "a.start, "
					+ "a.end, "
					+ "a.location, "
					+ "a.contactperson, "
					+ "a.description, "
					+ "c.id as c_id,"
					+ "c.title as c_title "
					+ "from APPOINTMENT as a left outer join CATEGORY as c "
					+ "on a.Category_id = c.id "
					+ "where a_title like ? "
					+ "or Description like ? "
					+ "or a.Location like ? "
					+ "or a.Contactperson like ? "
					+ "or a.Description like ? "
					+ "or c_Title like ?;");
			prep.setString(1, "%" + keyword + "%");
			prep.setString(2, "%" + keyword + "%");
			prep.setString(3, "%" + keyword + "%");
			prep.setString(4, "%" + keyword + "%");
			prep.setString(5, "%" + keyword + "%");
			prep.setString(6, "%" + keyword + "%");
			rs = prep.executeQuery();
			while (rs.next()) {
				Calendar startDate = util.CalendarConvert.convertDateStringToCalendar(rs.getString("start"));
				Calendar endDate = util.CalendarConvert.convertDateStringToCalendar(rs.getString("end"));
				CategoryDataModel category = null;
				for(CategoryDataModel c : categoryList) {
					if(c.getId() == rs.getInt("c_id")) {
						category = c;
					} else {
						c = null;
					}
				}
				result.add(new AppointmentDataModel(rs.getInt("a_Id"),rs.getString("a_title"), startDate,
						endDate, rs.getString("Location"), rs.getString("Contactperson"), category, 
						rs.getString("Description")));
			}
			conn.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		}

		return result;
	}

	// All category functions

	/**
	 * Function to add category.
	 * 
	 * @param title
	 */
	public void addCategory(String title){
		try{
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:"+ databaseName);
			PreparedStatement prep = conn.prepareStatement(
					"insert into CATEGORY (Title) values "
					+ "(?);");
			prep.setString(1, title);
			prep.addBatch();
			conn.setAutoCommit(false);
			prep.executeBatch();
			conn.setAutoCommit(true);
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Function to update category.
	 * 
	 * @param id The id of the category to update
	 * @param title The title of the category to update
	 */
	public void updateCategory(int id, String title) {
		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:"+ databaseName);
			prep = conn.prepareStatement(
			"update CATEGORY set title = ? where id = ?;");
			prep.setString(1, title);
			prep.setInt(2, id);
			prep.executeUpdate();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Function to delete category.
	 * 
	 * @param id
	 */
	public void deleteCategory(int id) {
		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:"+ databaseName);
			prep = conn.prepareStatement(
					"delete from CATEGORY where id = " + id + ";");
			prep.executeUpdate();
			prep = conn.prepareStatement(
					"UPDATE APPOINTMENT set Category_id = 0 where Category_id = " + id + ";");
			prep.executeUpdate();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Function to check if category exists in the database.
	 * 
	 * @param id
	 * @return boolean
	 */
	public boolean categoryExists(int id) {
		boolean cExists = false;
		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:"+ databaseName);
			prep = conn.prepareStatement("select * from CATEGORY where id = ?;");
			prep.setInt(1, id);
			rs = prep.executeQuery();
			cExists = rs.next();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return cExists;
	}

	/**
	 * Function to select categoryData from database.
	 * 
	 * @param id Contains the name of the table for the select query
	 */
	public CategoryDataModel selectCategory(int id) {
		CategoryDataModel wrapper = null;
		if(categoryExists(id)) {
			try {
				Class.forName("org.sqlite.JDBC");
				conn = DriverManager.getConnection("jdbc:sqlite:"+ databaseName);
				prep = conn.prepareStatement("select * from CATEGORY where id = ?;");
				prep.setInt(1, id);
				rs = prep.executeQuery();
				rs.next();
				wrapper = new model.CategoryDataModel(rs.getInt("id"),rs.getString("title"));

				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} finally {

			}
		}

		return wrapper;
	}

	/**
	 * Function to return all categories.
	 * 
	 * @return Vector<CategoryDataModel>
	 */
	public Vector<CategoryDataModel> selectAllCategories() {
		Vector<CategoryDataModel> list = new Vector<CategoryDataModel>();
		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:"+ databaseName);
			prep = conn.prepareStatement("select * from CATEGORY;");
			rs = prep.executeQuery();
			while (rs.next()) {
				list.add(new CategoryDataModel(rs.getInt("id"),rs.getString("title")));
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		return list;
	}
}